from flask import Blueprint, render_template, request, jsonify, session, redirect, url_for
import uuid
import random
import hashlib
import sqlite3
import os


# 创建蓝图
home_blueprint = Blueprint('home', __name__)
db_path = 'db/shop.db'

# 连接数据库
def get_db_connection():
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn

# 获取商品数据
def get_products():
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('SELECT * FROM products')
    products = c.fetchall()
    conn.close()
    return products

# 获取购物车数据
def get_cart():
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('SELECT p.name, p.price, c.quantity FROM cart c JOIN products p ON c.product_id = p.id')
    cart = c.fetchall()
    conn.close()
    return cart

# 添加商品到购物车
def add_to_cart(product_id, quantity):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('INSERT INTO cart (product_id, quantity) VALUES (?, ?)', (product_id, quantity))
    conn.commit()
    conn.close()

# 管理员功能：删除商品
def delete_product(product_id):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('DELETE FROM products WHERE id = ?', (product_id,))
    conn.commit()
    conn.close()

# 管理员功能：更新商品
def update_product(product_id, name, price, image):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('UPDATE products SET name = ?, price = ?, image = ? WHERE id = ?', (name, price, image, product_id))
    conn.commit()
    conn.close()

# 获取支付记录统计
def get_payment_stats():
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('SELECT status, COUNT(*) FROM payments GROUP BY status')
    stats = c.fetchall()
    conn.close()
    return stats

# 获取每个订单的总金额
def get_order_amounts(conn):
    c = conn.cursor()
    c.execute('''
        SELECT p.order_id, SUM(oi.total_price) AS total_amount
        FROM payments p
        JOIN order_items oi ON p.order_id = oi.order_id
        GROUP BY p.order_id;
    ''')
    return c.fetchall()

#获取不同商品的订单金额
def get_product_order_amounts_with_names(conn):
    c = conn.cursor()
    c.execute('''
        SELECT p.name AS product_name, SUM(oi.total_price) AS total_amount
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        GROUP BY oi.product_id;
    ''')
    return c.fetchall()


#获取商品被购买的次数
def get_product_purchase_count_with_names(conn):
    c = conn.cursor()
    c.execute('''
        SELECT p.name AS product_name, SUM(oi.quantity) AS purchase_count
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        GROUP BY oi.product_id;
    ''')
    return c.fetchall()





########################################################################################################################
# 首页（商品展示）
@home_blueprint.route('/')
def index():
    return render_template('index.html')

@home_blueprint.route('/goods')
def goods():
    products = get_products()
    
    return render_template('goods.html', products=products)

# 路由：加入购物车
@home_blueprint.route('/add_to_cart/<int:product_id>', methods=['POST'])
def add_to_cart(product_id):
    # 连接到数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    quantity = 1
    # 检查购物车中是否已存在该商品
    cursor.execute("SELECT * FROM cart WHERE product_id = ?", (product_id,))
    existing_item = cursor.fetchone()

    cursor.execute("SELECT * FROM products WHERE id = ?", (product_id,))
    good = cursor.fetchone()
    
    if existing_item:
        # 如果商品已经在购物车中，更新数量
        new_quantity = existing_item[2] + quantity
        cursor.execute("UPDATE cart SET quantity = ? WHERE product_id = ?", (new_quantity, product_id))
        print(f"更新商品 {product_id}，新数量为 {new_quantity}.")
    else:
        # 如果商品不在购物车中，插入新记录
        cursor.execute("INSERT INTO cart (product_id, quantity, price) VALUES (?, ?, ?)", (product_id, quantity, good[2]))
        print(f"商品 {product_id} 已加入购物车，数量为 {quantity}.")
    
    # 提交事务
    conn.commit()
    conn.close()

    return jsonify({'success': True, 'message': '商品已成功添加到购物车'})

# 购物车页面
@home_blueprint.route('/view_cart')
def view_cart():
    cart_items = get_cart()
    total_amount = sum([item[1] * item[2] for item in cart_items])
    # 生成一个随机的订单号
    order_id = str(uuid.uuid4())  # 使用 uuid 生成唯一订单ID

    return render_template('cart.html', cart=cart_items, total_amount=total_amount, order_id=order_id)

# 处理支付
@home_blueprint.route('/api/v1/payments', methods=['POST'])
def pay():
    order_id = request.form['orderId']
    amount = float(request.form['amount'])
    # 模拟支付成功/失败
    status = "success" if random.random() > 0.2 else "failure"
    payment_id = f"PAYMENT{random.randint(100000, 999999)}"
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    # 查询购物车中的所有商品
    c.execute('SELECT product_id, quantity, price FROM cart WHERE 1')
    cart_items = c.fetchall()

    if status == "success":
        # 记录支付数据
        c.execute('INSERT INTO payments (order_id, amount, status, payment_id) VALUES (?, ?, ?, ?)', 
                  (order_id, amount, status, payment_id))
        conn.commit()

        # 将购物车中的每个商品插入到订单明细中（假设有一个订单明细表 `order_items`）
        for item in cart_items:
            product_id, quantity, price = item
            total_price = quantity * price
            c.execute('INSERT INTO order_items (order_id, product_id, quantity, price, total_price) VALUES (?, ?, ?, ?, ?)',
                      (order_id, product_id, quantity, price, total_price))
        
        conn.commit()

        # 清空购物车
        c.execute('DELETE FROM cart WHERE 1')
        conn.commit()

    conn.close()
    
    return jsonify({
        'status': status,
        'paymentId': payment_id,
        'message': "支付成功" if status == "success" else "支付失败"
    })




###########################################################   管理员控制面板   ##############################################################
# 获取所有商品
@home_blueprint.route('/api/products', methods=['GET'])
def products_list():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('SELECT * FROM products')
    products = c.fetchall()
    conn.close()
    return jsonify([dict(product) for product in products])

# 添加商品
@home_blueprint.route('/api/products', methods=['POST'])
def add_product():
    name = request.json.get('name')
    price = request.json.get('price')
    image = request.json.get('image')
    
    if not name or not price or not image:
        return jsonify({'error': 'Missing fields'}), 400

    conn = get_db_connection()
    c = conn.cursor()
    c.execute('INSERT INTO products (name, price, image) VALUES (?, ?, ?)', (name, price, image))
    conn.commit()
    conn.close()

    return jsonify({'message': 'Product added successfully'}), 201

# 更新商品
@home_blueprint.route('/api/products/<int:id>', methods=['PUT'])
def update_product(id):
    name = request.json.get('name')
    price = request.json.get('price')

    if not name or not price:
        return jsonify({'error': 'Missing fields'}), 400

    conn = get_db_connection()
    c = conn.cursor()
    c.execute('UPDATE products SET name = ?, price = ? WHERE id = ?', (name, price, id))
    conn.commit()
    conn.close()

    return jsonify({'message': 'Product updated successfully'}), 200

# 删除商品
@home_blueprint.route('/api/products/<int:id>', methods=['DELETE'])
def delete_product(id):
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('DELETE FROM products WHERE id = ?', (id,))
    conn.commit()
    conn.close()

    return jsonify({'message': 'Product deleted successfully'}), 200

# 数据统计页面
@home_blueprint.route('/admin')
def admin():
    return render_template('admin.html')


# 数据统计页面
@home_blueprint.route('/stats')
def stats():
    # 获取统计数据
    conn = get_db_connection()
    order_amounts = get_order_amounts(conn)
    product_order_amounts = get_product_order_amounts_with_names(conn)
    product_purchase_count = get_product_purchase_count_with_names(conn)
    conn.close()

    # 转化为前端 ECharts 需要的格式
    order_amounts_data = [{'order_id': row['order_id'], 'total_amount': row['total_amount']} for row in order_amounts]
    product_order_amounts_data = [{'product_name': row['product_name'], 'total_amount': row['total_amount']} for row in product_order_amounts]
    product_purchase_count_data = [{'product_name': row['product_name'], 'purchase_count': row['purchase_count']} for row in product_purchase_count]

    return render_template('stats.html', order_amounts_data=order_amounts_data,
                           product_order_amounts_data=product_order_amounts_data,
                           product_purchase_count_data=product_purchase_count_data)



# 选择用户类型（普通用户或管理员）
@home_blueprint.route('/user/<user_type>')
def choose_user(user_type):
    if user_type == '1':
        return redirect(url_for('home.goods'))  # 普通用户直接进入首页
    elif user_type == '2':
        return redirect(url_for('home.admin'))  # 管理员进入控制面板
    else:
        return '无效的用户类型', 400